﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using cecbData;

namespace Dss.Methods
{
     public class MemberInfo
    {
         public MemberInfo() {

             cecbContext = new DSSEntities();
         }

         // get clas objects to use in insert member method
         DSSEntities cecbContext;
         Members mem;
         MemberContacts memContactNo;
         MemberEmails memEmail;
         MemberLogin memLogin;

         // insert member method 
         public bool InsertMember(String name, String address, String designation, String specilised, String contactNo, String email)
         {
             bool IsInserted = false;
             try
             {
                 String reference = getNewMemberReference();
                 mem = new Members();
                 mem.mem_reference = reference;
                 mem.mem_name = name;
                 mem.mem_designation = designation;
                 mem.mem_address = address;
                 //  mem.mem_specilised = specilised;

                 cecbContext.AddToMembers(mem);
                 cecbContext.SaveChanges();

                 // insert data to the EmployeeContact table
                 memContactNo = new MemberContacts();
                 memContactNo.mem_reference = reference;
                 memContactNo.mem_contactNo = contactNo;
                 cecbContext.AddToMemberContacts(memContactNo);
                 cecbContext.SaveChanges();

                 // insert data to the Employee email table
                 memEmail = new MemberEmails();
                 memEmail.mem_reference = reference;
                 memEmail.mem_email = email;
                 cecbContext.AddToMemberEmails(memEmail);
                 cecbContext.SaveChanges();



                 IsInserted = true;
             }
             catch (Exception)
             {

                 IsInserted = false;
             }


             return IsInserted;
         }
         // get all informations from member table
         public List<Members> getAllMembers()
         {
             IQueryable<Members> query = from c in cecbContext.Members

                                         select c;

             List<Members> Memdetails = query.ToList();

             return Memdetails;
         }
         // get the member contact details trough mem_reference
         public List<MemberContacts> getMemberContatcs(String memId)
         {
             IQueryable<MemberContacts> query = from c in cecbContext.MemberContacts

                                                where c.mem_reference == memId
                                                select c;

             List<MemberContacts> Memdetails = query.ToList();

             return Memdetails;
         }

         // get the member Email details through the mem_referance
         public List<MemberEmails> getMemberEmail(String memId)
         {
             IQueryable<MemberEmails> query = from c in cecbContext.MemberEmails
                                              where c.mem_reference == memId
                                              select c;

             List<MemberEmails> Memdetails = query.ToList();

             return Memdetails;
         }
         // insert consultant basic informations
         public bool InsertConsultantBasicInfo(String name, String address, String designation, String specilised, List<String> cno, List<String> em, String specilized)
         {
             bool IsInserted = false;
             try
             {
                 String reference = getNewMemberReference();
                 mem = new Members();
                 mem.mem_reference = reference;
                 mem.mem_name = name;
                 mem.mem_designation = designation;
                 mem.mem_address = address;
                 mem.mem_areaOfSpec = specilised;
                 // mem.mem_specilised = specilised;


                 cecbContext.AddToMembers(mem);
                 cecbContext.SaveChanges();




                 foreach (String no in cno)
                 {
                     memContactNo = new MemberContacts();
                     memContactNo.mem_reference = reference;
                     memContactNo.mem_contactNo = no;
                     cecbContext.AddToMemberContacts(memContactNo);
                     cecbContext.SaveChanges();
                 }

                 foreach (String mail in em)
                 {
                     memEmail = new MemberEmails();
                     memEmail.mem_reference = reference;
                     memEmail.mem_email = mail;
                     cecbContext.AddToMemberEmails(memEmail);
                     cecbContext.SaveChanges();
                 }



                 IsInserted = true;
             }
             catch (Exception)
             {

                 IsInserted = false;
             }


             return IsInserted;
         }



         // generate the member refarance
         public String getNewMemberReference()
         {

             var idKey = (from c in cecbContext.Members
                          orderby c.mem_id descending
                          select c).First();


             String id = idKey.mem_reference.ToString();
             int sub;
             try
             {
                 sub = int.Parse(id.Substring(3)) + 1;
             }
             catch (Exception)
             {
                 sub = 1;
             }
             String newId = "MEM" + sub;
             return newId.ToString();

         }
         // get data of member specialized area as a list
         public List<Components> getSpecializatioInformations()
         {
             IQueryable<Components> Query =
                   from c in cecbContext.Components

                   select c;

             List<Components> SelectedEmpList = Query.ToList();

             return SelectedEmpList;

         }
         // update the member Email details
         public bool updateMemberEmails(String eno, String memid, String oldEno)
         {
             bool IsUpdated = false;
             try
             {
                 cecbContext.ExecuteStoreCommand("update MemberEmails set mem_email ='" + eno + "' where mem_reference = '" + memid + "' and mem_email ='" + oldEno + "' ");
                 IsUpdated = true;
             }
             catch (Exception)
             {

                 IsUpdated = false;
             }

             return IsUpdated;

         }
         // update the member contact details
         public bool updateMemberContact(String eno, String memid, String oldEno)
         {
             bool IsUpdated = false;
             try
             {
                 cecbContext.ExecuteStoreCommand("update MemberContacts set mem_contactNo ='" + eno + "' where mem_reference = '" + memid + "' and mem_contactNo ='" + oldEno + "' ");
                 IsUpdated = true;
             }
             catch (Exception)
             {

                 IsUpdated = false;
             }

             return IsUpdated;

         }
         //get the member name through member referance
         public string getMemberName(String memId)
         {
             string name = "";
             try
             {

                 var quary = (from c in cecbContext.Members
                              where c.mem_reference == memId
                              select c.mem_name).First();
                 name = quary;


             }
             catch (Exception)
             {

             }
             return name;
         }
         // get member details as a list through member referance
         public List<Members> getMemberDetails(string mem_id)
         {
             IQueryable<Members> query = from c in cecbContext.Members
                                         where c.mem_reference == mem_id

                                         select c;

             List<Members> Memdetails = query.ToList();

             return Memdetails;
         }

         // To get the member login details
         public List<MemberLogin> getMemberLoginDetails(string mem_id)
         {
             IQueryable<MemberLogin> query = from c in cecbContext.MemberLogin
                                             where c.mem_reference == mem_id

                                             select c;

             List<MemberLogin> Memdetails = query.ToList();

             return Memdetails;
         }

         // update the member details

         public bool updateMemberDetails(string mem_ref, string mem_address, string mem_name, string mem_designation, string mem_specilization)
         {
             bool isselected = false;
             try
             {


                 Members memberObject = cecbContext.Members.First(i => i.mem_reference == mem_ref);

                 memberObject.mem_address = mem_address;
                 memberObject.mem_name = mem_name;
                 memberObject.mem_designation = mem_designation;
                 memberObject.mem_areaOfSpec = mem_specilization;
                 cecbContext.SaveChanges();
                 isselected = true;

             }
             catch (Exception)
             {

             }

             return isselected;

         }
         // update the member contact details
         public void uodateMemberContactDetails(string memId, string Eaddress)
         {
             try
             {

                 MemberEmails memEmail = cecbContext.MemberEmails.First(i => i.mem_reference == memId);
                 memEmail.mem_email = Eaddress;
                 cecbContext.SaveChanges();
             }
             catch (Exception)
             {


             }
         }

         public bool insertTeamLeadeaderInformations(String name, String address, String designation, String specilised, List<String> cno, List<String> em, String username, String passwaord, String specilized)
         {
             bool IsInserted = false;
             try
             {
                 String reference = getNewMemberReference();
                 mem = new Members();
                 mem.mem_reference = reference;
                 mem.mem_name = name;
                 mem.mem_designation = designation;
                 mem.mem_address = address;
                 mem.mem_areaOfSpec = specilized;
                 // mem.mem_specilised = specilised;


                 cecbContext.AddToMembers(mem);
                 cecbContext.SaveChanges();


                 // insert data to the Employee login table
                 memLogin = new MemberLogin();
                 memLogin.mem_reference = reference;
                 memLogin.login_username = username;
                 memLogin.login_password = passwaord;
                 cecbContext.AddToMemberLogin(memLogin);
                 cecbContext.SaveChanges();


                 foreach (String no in cno)
                 {
                     memContactNo = new MemberContacts();
                     memContactNo.mem_reference = reference;
                     memContactNo.mem_contactNo = no;
                     cecbContext.AddToMemberContacts(memContactNo);
                     cecbContext.SaveChanges();
                 }

                 foreach (String mail in em)
                 {
                     memEmail = new MemberEmails();
                     memEmail.mem_reference = reference;
                     memEmail.mem_email = mail;
                     cecbContext.AddToMemberEmails(memEmail);
                     cecbContext.SaveChanges();
                 }







                 IsInserted = true;
             }
             catch (Exception)
             {

                 IsInserted = false;
             }


             return IsInserted;
         }




    }
 }